package com.odontosis.dao;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.odontosis.as.OdontosisDataAccessObject;
import com.odontosis.entidade.Paciente;
import com.odontosis.entidade.Procedimento;
import com.odontosis.entidade.ServicoConveniado;
import com.odontosis.util.HibernateUtil;
import com.odontosis.util.StringUtilsOdontosis;

public class ServicoConveniadoDAO extends OdontosisDataAccessObject<ServicoConveniado> {

	public Collection<ServicoConveniado> pesquisarPorNomePaciente(String nome){
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		Criteria criteria = session.createCriteria(ServicoConveniado.class);
		criteria.createAlias("paciente", "p");
		if(!StringUtilsOdontosis.isVazia(nome)){
			criteria.add(Restrictions.like("p.nome", nome, MatchMode.ANYWHERE));
		}
		criteria.add(Restrictions.eq("ativo", true));
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		return criteria.list();
	}
	
	@SuppressWarnings("unchecked")
	public Collection<ServicoConveniado> pesquisarPorPacientePastaPeriodo(String nomePaciente, String pasta, Date inicio, Date fim){
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		Criteria criteria = session.createCriteria(ServicoConveniado.class);
		criteria.createAlias("paciente", "p");
		if(!StringUtilsOdontosis.isVazia(nomePaciente)){
			criteria.add(Restrictions.like("p.nome", nomePaciente, MatchMode.ANYWHERE));
		}
		if(!StringUtilsOdontosis.isVazia(pasta)){
			criteria.add(Restrictions.like("p.numeroPasta", pasta));
		}
		if(inicio != null && fim != null){
			criteria.add(Restrictions.between("data", inicio, fim));
		}
		criteria.add(Restrictions.eq("ativo", true));
		criteria.addOrder(Order.desc("id"));
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		return criteria.list();
	}
	
	public Collection<ServicoConveniado> pesquisarPorDataECodigo(Date inicio, Date fim, String codigo, boolean pericia){
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		Criteria criteria = session.createCriteria(ServicoConveniado.class);
		criteria.createAlias("listaProcedimentos", "s");
		criteria.add(Restrictions.between("data", inicio, fim));
		if(pericia) {
			criteria.add(Restrictions.isNotNull("dataPericia"));
		} else {
			criteria.add(Restrictions.isNull("dataPericia"));
		}
		if(!StringUtilsOdontosis.isVazia(codigo)){
			criteria.add(Restrictions.eq("s.codigo", codigo));
		}
		
		criteria.add(Restrictions.eq("ativo", true));
		criteria.addOrder(Order.asc("fiod"));
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		return criteria.list();
	}
	
	public Long numeroParcelasJaGeradas(Procedimento procedimento, Paciente paciente) {
		 HibernateUtil.closeSession();
		 Session session = HibernateUtil.currentSession();
		 String sql = " SELECT max(sc.parcela) FROM servicoconveniado sc "
			 +" INNER JOIN servicoconveniado_procedimento scp ON (scp.servicoconveniado = sc.id) "
			 +" INNER JOIN procedimento p ON (scp.procedimento = p.id) "
			 +" WHERE sc.paciente = " + paciente.getId()
			 +" AND p.id = " + procedimento.getId()
			 +" AND sc.ativo = 1 ";
		 Query query = session.createSQLQuery(sql);
		Integer uniqueResult = (Integer) query.uniqueResult();
		 if(uniqueResult == null) {
			 return 0L;
		 }
		return uniqueResult.longValue();
	}
	
	public List<ServicoConveniado> buscarServicoConveniado(Date inicio, Date fim, String ipsms) {
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		
		String hql = "Select sc from ServicoConveniado sc" +
				" where sc.data between :inicio and :fim ) ";
		if(!StringUtilsOdontosis.isVazia(ipsms)) {
			hql += " and sc.paciente.ipsm in ("+ ipsms+") ";
		}
		hql += " order by sc.data asc";
		Query query = session.createQuery(hql);
		query.setParameter("inicio", inicio);
		query.setParameter("fim", fim);
		return query.list();
	}
	
	public List<Paciente> buscarPacientes(String ipsms, boolean virNoRelatorio) {
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		
		String hql = " from Paciente p " +
				" where p.virNoRelatorio = :virNoRelatorio " +
				" and ativo = true " +
				" and ipsm <> '' " +
				" and ipsm is not null";
		if(!StringUtilsOdontosis.isVazia(ipsms)) {
			hql += " and p.ipsm in ("+ ipsms+") ";
		}
		hql += " order by p.nome asc";
		
		Query query = session.createQuery(hql);
		query.setParameter("virNoRelatorio", virNoRelatorio);
		return query.list();
		
	}
	
	/*public Collection<Object[]> pesquisarPorPeriodoPacienteSemFIOD(Date inicio, Date fim, String ipsms) {
		HibernateUtil.closeSession();
		Session session = HibernateUtil.currentSession();
		
		String hql = "Select p from Paciente p" +
				" where " +
				"   p.virNoRelatorio = true " +
				"   and p.ipsm is not null " +
				"   and p.ipsm <> '' " +
				"   and not exists (Select sc from ServicoConveniado sc " +
				"							 where sc.paciente.id = p.id " +
				"                              and sc.data between :inicio and :fim )";
		if(!StringUtilsOdontosis.isVazia(ipsms)) {
			hql += " and p.ipsm in ("+ ipsms+") ";
		}
		hql += " order by "
		return null;
	}*/
	
}
